package com.car.excel;

import com.car.dao.EmployeeDao;
import com.car.dao.impl.EmployeeDaoImpl;
import com.car.utils.JDBCUtil;
import com.car.utils.NumberUtil;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashSet;
import java.util.Set;


public class EmployeeExcel {
    /**
     * 加载Excel文件 并将数据写入Mysql中
     *
     * @param fileUrl 文件路径 如：excel/员工表.xlsx
     * @throws Exception 异常
     */
    public static <XSSFWorkbook, XSSFSheet> void loadExcelAndWriteInMySQL(String fileUrl) throws Exception {
        InputStream inputStream = new FileInputStream(fileUrl);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
        // 定义行变量
        int rowIndex = 1;
        // 查询workId 避免重复问题
        EmployeeDao employeeDao = new EmployeeDaoImpl();
        Set<String> workIds = employeeDao.findWorKIds();
        if (workIds == null) {
            workIds = new HashSet<>();
        }
        while (true) {

                break;
            }
            // 这一行第一列记录
        Class<Object> row = null;
        Class<?> c0 = row.getDeclaringClass();
            String username = c0.getName();

            Class<?> c1 = row.getDeclaringClass();
            double age = Double.parseDouble(c1.getName());

            Class<?> c2 = row.getDeclaringClass();
            String gender = c2.getName();

            Class<?> c4 = row.getDeclaringClass();
            double salary = Double.parseDouble(c4.getName());
            //判断workId是否重复
            String workId;
            while (true) {
                workId = NumberUtil.generateWorkId();
                if (!workIds.contains(workId)) {
                    workIds.add(workId);
                    break;
                }
            }
            // 定义插入语句
            String insertSQL = "insert into t_employee(name,age,gender,work_id,salary) VALUE (?,?,?,?,?)";
            JDBCUtil.update(insertSQL, username, age, gender
                    , workId, salary);
            // 跳到下一行记录
            rowIndex++;
        }
    }





